﻿Imports BE.BE
Imports DAL.DAL


Public Class DAL_Bitacora

    Shared Function listarTodos() As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Ds = Helper_DAL.ExecuteDataset("SELECT * FROM bitacora WHERE borrado=0 ORDER BY id_bitacora DESC")
        Dim lista As New List(Of Bitacora_BE)

        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.DVH = dr.Item(5)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarIntervalo(ByVal pfechainicio As String, ByVal pfechafin As String) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim strcommn As String = "SELECT * FROM bitacora WHERE fecha_log BETWEEN '" & pfechainicio & " 00:00:00' AND '" & pfechafin & " 23:59:59' AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)

        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.Borrado = dr.Item(5)
            obita.DVH = dr.Item(6)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarPorUsuario(ByVal pusuario As String) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim usr As New Usuario_BE
        usr = DAL_Usuario.traerUsuario(pusuario)
        Dim strcommn As String = "SELECT * FROM bitacora WHERE id_usuario = " & usr.id & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)

        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.Borrado = dr.Item(5)
            obita.DVH = dr.Item(6)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarPorCriticidad(ByVal pcriticidad As Integer) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim strcommn As String = "SELECT * FROM bitacora WHERE criticidad = " & pcriticidad & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)

        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.Borrado = dr.Item(5)
            obita.DVH = dr.Item(6)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarCombinadoRangoFechasUsuario(ByVal pFechaInicioFiltrar As String, ByVal pFechaFinalFiltrar As String, ByVal pUsuario As String) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim usr As New Usuario_BE
        usr = DAL_Usuario.traerUsuario(pUsuario)
        Dim strcommn As String = "SELECT * FROM bitacora WHERE fecha_log BETWEEN '" & pFechaInicioFiltrar & "  00:00:00' AND '" & pFechaFinalFiltrar & " 23:59:59' AND id_usuario = " _
                                       & usr.id & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)
        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.DVH = dr.Item(5)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarCombinadoRangoFechasCriticidad(ByVal pFechaInicioFiltrar As String, ByVal pFechaFinalFiltrar As String, ByVal pCriticidad As Integer) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim strcommn As String = "SELECT * FROM bitacora WHERE fecha_log BETWEEN '" & pFechaInicioFiltrar & "  00:00:00' AND '" & pFechaFinalFiltrar & " 23:59:59' AND criticidad = " _
                                       & pCriticidad & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)
        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.DVH = dr.Item(5)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarCombinadoUsuarioCriticidad(ByVal pUsuario As String, ByVal pCriticidad As Integer) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim usr As New Usuario_BE
        usr = DAL_Usuario.traerUsuario(pUsuario)
        Dim strcommn As String = "SELECT * FROM bitacora WHERE id_usuario = " & usr.id & " AND criticidad = " & pCriticidad & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)
        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.DVH = dr.Item(5)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Function listarCombinadoRangoFechasUsuarioCriticidad(ByVal pFechaInicioFiltrar As String, ByVal pFechaFinalFiltrar As String, ByVal pUsuario As String, ByVal pCriticidad As Integer) As List(Of Bitacora_BE)
        Dim Ds As New DataSet
        Dim usr As New Usuario_BE
        usr = DAL_Usuario.traerUsuario(pUsuario)
        Dim strcommn As String = "SELECT * FROM bitacora WHERE fecha_log BETWEEN '" & pFechaInicioFiltrar & "  00:00:00' AND '" & pFechaFinalFiltrar & " 23:59:59' AND id_usuario = " _
                                       & usr.id & " AND criticidad = " & pCriticidad & " AND borrado=0 ORDER BY id_bitacora DESC"
        Ds = Helper_DAL.ExecuteDataset(strcommn)
        Dim lista As New List(Of Bitacora_BE)
        For Each dr As DataRow In Ds.Tables(0).Rows
            Dim obita As New Bitacora_BE
            obita.Id = dr.Item(0)
            obita.IdUser = dr.Item(1)
            obita.Nick = ""
            obita.Fecha = dr.Item(2)
            obita.Descripcion = dr.Item(3).ToString
            obita.Criticidad = dr.Item(4)
            obita.DVH = dr.Item(5)
            lista.Add(obita)
        Next
        Return lista
    End Function


    Shared Sub depurarBitacora(ByVal pFechaInicio As String, ByVal pFechaFin As String)
        Dim strcommn As String = "UPDATE bitacora SET borrado=1 WHERE fecha_log BETWEEN '" & pFechaInicio & "  00:00:00' AND '" & pFechaFin & " 23:59:59'"
        Helper_DAL.ExecuteNonQuery(strcommn)
    End Sub


    Shared Function grabarEnBitacora(pIdUsuario As Integer, pMovimiento As TipoMovimiento_BE, pfecha As DateTime, pDVH As Integer) As Boolean

        Dim mNuevoId As Integer = ObtenerProximoId()
        Dim mComando As String = ("insert into Bitacora (id_bitacora, id_usuario, fecha_log, descripcion, criticidad, borrado, dvh) values " & _
                                    "(" & mNuevoId & "," & pIdUsuario & ",'" & pfecha & "','" & pMovimiento.mDescripcion & "'," & pMovimiento.mCriticidad & ",'false', " & pDVH & ")")
        Try
            Helper_DAL.ExecuteScalar(mComando)
        Catch ex As Exception
            Throw ex
            Return False
        End Try
        Return True

    End Function



    Shared Function obtenerProximoId() As Integer
        Dim maxid As Integer = 0
        Try
            maxid = Helper_DAL.ObtenerProximoId("bitacora")
        Catch ex As Exception
            Throw ex
        End Try
        Return maxid
    End Function


    'Shared Sub ActualizaDVV(pTabla As String)
    '    Dim DVV As Integer = 0
    '    Try
    '        'calculo la sumatoria de todos los dvh de la tabla.
    '        DVV = Helper_DAL.ExecuteScalar("SELECT SUM(DVH) FROM " & pTabla)
    '        'actualizo el valor del dvv de la tabla.
    '        Helper_DAL.ExecuteScalar("UPDATE DVV SET valor= " & DVV & " WHERE TABLA = '" & pTabla & "'")
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    'End Sub


    'Shared Function CalcularDVV(pTabla) As Integer
    '    Dim DVV As Integer = 0
    '    '        Dim mNuevoId As Integer = ObtenerProximoId()
    '    'ver de hacer solo el update del valor.

    '    Try
    '        'calculamos el dvv de la tabla.
    '        DVV = Helper_DAL.ExecuteScalar("SELECT SUM(DVH) FROM " & pTabla)
    '        'actualizamos la tabla de dvv.
    '        Helper_DAL.ExecuteNonQuery("UPDATE DVV (valor,tabla) VALUES (" & DVV & ",'" & pTabla & "')")
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return DVV
    'End Function

End Class
